/********************************************************************
Renaming the Serrano file and mergint the UC and the Serrano database: basic firm data
**********************************************************************/

/*Preparing the Serrano file for matching with UC*/
clear all

*cd "/RFS_replication_package/Martinsson_et_al_datasets"

use "serrano_20190216.dta" /*Load Serrano data*/

*We drop variables that are not relevant for the current project and rename some variables to match the database with UC. In some cases, we copy a firm characteristics and rename a variable so that the name is self-explanatory*/

rename tillgsu ta   /*total assets*/

rename redtyp redovtyp /*accounting type*/

rename bslslut bksdatt  /*firm year end*/
rename bslstart bksdatf 	/*firm year start*/
tsset bidnr year 
		 
		 *use all assets
		 
		 
		 **************************ASSETS

replace material_assets=0 if material_assets==.
label var material_assets "ppe"


egen receivable_customers = rowtotal(kundford ) /*Kundford: current receivables from the company's customers*/
drop kundford
replace receivable_customers=0 if receivable_customers==.

egen cash_sec= rowtotal(kplacsu )  /*kplacsu: investments in securities*/
replace cash_sec = 0 if cash_sec==.
drop kplacsu

/*g ta=tillgsu*/
label var ta "total_assets"
recast double ta

*egen ta = rowtotal(cash_sec total_fixed_assets inventory receivable_customers)

g total_current_assets=omstgsu
drop omstgsu


**************Debt!!

													g acc_pay=ksklev    /*account payable*/
													drop ksklev



													gen xternal_long_debt=lsksu
													drop lsksu

													replace xternal_long_debt=0 if xternal_long_debt==.

													*short term debt


													

													gen xternal_short_debt = ksksu
													drop ksksu
													**accounts payable********************
													label var acc_pay "accounts payable, lev kred"



													egen net_working_capital=rowtotal(total_current_assets cash_sec acc_pay -xternal_short_debt)


													***************
													*total debt
													egen total_debt = rowtotal(xternal_short_debt xternal_long_debt)

g or_r_leverage = total_debt/ta





********************************************


	g interest=rtekoext  /*external interest expenses*/
	
	
	egen net_financial_res = rowtotal(rteinext -rtekoext) /*rteinext: external interest income, net_financial_res is the difference between external interest income and costs*/
label var net_financial_res "fin income minus fin costs"
	
rename anltsu matanlsu /*anltsu: total fixed assets*/
	
	 
	 g ppe=matanlsu   /*ppe: property, plant and equipment; matanlsu: tangible fixed assets*/
	 
	 
	 label var ppe "prop plant eq"
	 

	
	 
	 
	 
	 g or_lcash=log(cash_sec)
	 
	 

rename avskriv depreciation_all  /*avskriv stands for avskrivningar, which is depreciation in Swedish*/

		label var depreciation_all "depreciations"
		sort bidnr year, stable

		g investment_net=(ppe-l.ppe)+depreciation_all
		label var investment_net "good capx"

		
		

			g cash_flow=rorresul+depreciation_all /*rorresul (rorelseresultat in Swedish): EBIT*/

		 g investment_gross=(ppe-l.ppe)
		 label var investment_gross "change in ppe ign depr"
		 
		gen ebit =rorresul	
		drop rorresul
		
		 
rename new_bransch rbsni  /*NACE codes*/
		g return_net= resefin /*Profit/Loss after net financial income/expenses*/
		
/*Revising format: the format is string in the UC, and it is long in serrano (displayed as series of numbers). I divided the date into 3 parts, and constructed the corresponding string date that is similar to the UC in terms of format*/
format bksdatf bksdatt %td   /*bksdatf variable is bokforingsdatum or accounting date*/
	g bksdatf_year=year(bksdatf)
	g bksdatf_month=month(bksdatf)
	g bksdatf_day=day(bksdatf)
	tostring bksdatf_day, replace
	tostring bksdatf_month, replace
	tostring bksdatf_year, replace
	
g bksdatf_str=bksdatf_year +"-"+bksdatf_month+"-"+bksdatf_day  
drop bksdatf
rename bksdatf_str bksdatf

	g bksdatt_year=year(bksdatt)
	g bksdatt_month=month(bksdatt)
	g bksdatt_day=day(bksdatt)
	tostring bksdatt_day, replace
	tostring bksdatt_month, replace
	tostring bksdatt_year, replace
	
g bksdatt_str=bksdatt_year +"-"+bksdatt_month+"-"+bksdatt_day  
drop bksdatt
rename bksdatt_str bksdatt

drop bksdatf_day bksdatf_month bksdatf_year bksdatt_day bksdatt_month bksdatt_year  /*I delete the supplementary, assistant variables*/

sort bidnr year, stable

quietly by bidnr year: gen dup=cond(_N==1,0,_n)

drop if dup>1
drop dup


*The code so far produces the Serrano file we use for matching with UC* 
save serrano_renamed_20190216.dta, replace

/*Merging UC and Serrano*/
**# Bookmark #1

use "uc_2019_renamed.dta", replace  /*UC data*/
g days = bksdatt - bksdatf
merge 1:1 bidnr year using serrano_renamed_20190216.dta,force

duplicates tag bidnr year,generate(dup)

table dup  /*No duplicates*/

drop dup _merge

save merge_UC_Serrano_20190218.dta, replace   /*This yields the merged UC and Serrano file that will be extended with firm characteristics below*/

		
*Merging the matched UC-Serrano file with firm characteristics*/
clear all

use "merge_UC_Serrano_20190218.dta", clear


*labels 
label var interest "gross fin costs"
replace interest=interest*-1 if year>1996 /*negative values need to be fixed*/
label var interest "gross fin costs rtekoext"
replace depreciation_all = depreciation_all *-1 if year>1996 /*negative values need to be fixed*/ 
replace skatter = skatter *-1 if year>1996 /*negative values need to be fixed*/ /*skatter: taxes*/
replace perskos = perskos *-1 if year>1996 /*negative values need to be fixed*/  /*Personal costs*/
g ebitda = ebit+ depreciation_all if year>1996 /*negative values need to be fixed*/
label var eksu "equity"
drop cash_flow
g cash_flow=ebit+depreciation_all


duplicates tag bidnr year, g(tag)
sum tag
drop tag

*In the final section of the code, we finalize the datafile by cleaning the variable on number of employees and merging it with the emission data. The latter step is necessary to  make sure we will not miss firms with available fossil emissions. */

*Cleaning number of employees variable*/
merge 1:1 bidnr year using "basic_antanst_etc.dta"  /*This file (number of employees) is produced based on the aggregation of the installation-level data to firm level*/
replace antanst = ser_antanst if year>1996 /*We replace the number of employees with the one in Serrano after 1996 (when Serrano has a good coverage)*/
drop ser_antanst
/*drop _merge*/

save  "merge_UC_Serrano_20190218_trans.dta" , replace  /*We save the merged Serrano-UC-#employee data*/
use "merge_UC_Serrano_20190218_trans.dta" , clear

drop if _merge==2
drop _merge

merge 1:1 bidnr year using   "emissions_basic.dta"  /*Match firm-level fossil CO2 emissions*/
sort bidnr year 
drop if bidnr == .
drop _merge
save  "merge_UC_Serrano_firm_characteristics.dta", replace
